package org.adw.launcher2.ld.secret.db;

import java.util.LinkedList;
import java.util.List;
import org.adw.launcher2.ld.secret.dom.MyContact;
import org.adw.launcher2.ld.secret.dom.contact.EmailInfo;
import org.adw.launcher2.ld.secret.dom.contact.ImInfo;
import org.adw.launcher2.ld.secret.dom.contact.OrganizationInfo;
import org.adw.launcher2.ld.secret.dom.contact.PhoneInfo;
import org.adw.launcher2.ld.secret.dom.contact.PostalAddressInfo;
import org.adw.launcher2.ld.secret.dom.contact.UserInfo;
import org.adw.launcher2.ld.secret.dom.contact.WebsiteInfo;
import org.adw.launcher2.ld.secret.ui.utils.Global;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class MyContactCache {
	private static MyContactCache instance = null;
	private static final String TABLE_USER ="user";
	private SQLiteDatabase db;
	public static MyContactCache getInstance(DatabaseHelper dbHelper) {
		if (instance == null) {
			instance = new MyContactCache(dbHelper);
		}
		return instance;
	}
	
	private MyContactCache(DatabaseHelper dbHelper) {
		db = dbHelper.db;
		createTableIfNecessary();
	}
	private void createTableIfNecessary() {
		if (!Global.dbHelper.isTableExists(TABLE_USER)) {
			onCreateAllTable();
			
		}
	}
	
	public MyContact getContactById(long id) {	
		UserInfo user = getUserInfo(id);
		List<PhoneInfo> phone = getPhoneInfo(id);
		List<EmailInfo> email = getEmailInfo(id);
		List<OrganizationInfo> org = getOrg(id);
		List<ImInfo> im = getIM(id);
		List<PostalAddressInfo> pa = getPA(id);
		List<WebsiteInfo> web = getWebsite(id);
		MyContact myContact = new MyContact(user, phone, email, pa, org, im, web);
		myContact.id = user.getUserId();
		return myContact;
	}
	
	public List<MyContact> getAllMyContacts() {
		List<MyContact> list = new LinkedList<MyContact>();
		Cursor cursor = db.rawQuery("SELECT * FROM user", null);
		while(cursor.moveToNext()){
			long id = cursor.getLong(cursor.getColumnIndex("id"));
			UserInfo user = getUserInfo(id);
			List<PhoneInfo> phone = getPhoneInfo(id);
			List<EmailInfo> email = getEmailInfo(id);
			List<OrganizationInfo> org = getOrg(id);
			List<ImInfo> im = getIM(id);
			List<PostalAddressInfo> pa = getPA(id);
			List<WebsiteInfo> web = getWebsite(id);
			MyContact myContact = new MyContact(user, phone, email, pa, org, im, web);
			// assign myContact ID  = userId
			myContact.id = id;
			list.add(myContact);
		}
		cursor.close();	
		return list;
	}
	
	public void insertContactToCache(MyContact myContact) {
		// TODO (vanloi999) insert a contact to cache
		// when we have new contact added to Android contact
		// then we use this function to update the cache
		insertUser(myContact.getUserInfo());
		insertPhones(myContact.getListPhone());
		insertEmails(myContact.getListEmail());
		insertIMs(myContact.getListIm());
		insertOrgs(myContact.getListOrg());
		insertPAs(myContact.getListPA());
		insertWebsites(myContact.getListWeb());
		
	}
	
	public void insertListContactsToCache(final List<MyContact> contacts) {
		// TODO (vanloi999) insert list of contacts to cache
		// when we query content provider and have a list of contacts
		// use this function to insert contacts to cache
		new DbBatchOperation(db) {
			
			@Override
			public void doBatchInsert() {
				for(int i=0;i<contacts.size();i++){
					MyContact myContact = contacts.get(i);
					insertUser(myContact.getUserInfo());
					insertPhones(myContact.getListPhone());
					insertEmails(myContact.getListEmail());
					insertIMs(myContact.getListIm());
					insertOrgs(myContact.getListOrg());
					insertPAs(myContact.getListPA());
					insertWebsites(myContact.getListWeb());
				}
			}
		};
	}
	public void insertUser(UserInfo userInfo){// insert 1 user vao DB BkitMobile
		try{
			db.execSQL(Global.insertIntoUser, userInfo.getAllValue());
			}catch(SQLException ex){
				ex.printStackTrace();
			}
	}

	public void insertUsers(List<UserInfo> list) {// insert 1 list cac user vao DB Bkitmobile
		for(int i = 0; i< list.size();i++){
			try{
				db.execSQL(Global.insertIntoUser, list.get(i).getAllValue());
				}catch(SQLException ex){
					ex.printStackTrace();
				}
		}
	}
	public void insertPhones(List<PhoneInfo> list){// insert 1 list cac phone vao DB Bkitmobile
		for(int i = 0; i< list.size();i++){
			try{
				db.execSQL(Global.insertIntoPhone, list.get(i).getAllValue());
				}catch(SQLException ex){
					ex.printStackTrace();
				}
		}
	}
	public void insertEmails(List<EmailInfo> list){
		for(int i = 0; i< list.size();i++){
			try{
				db.execSQL(Global.insertIntoEmail, list.get(i).getAllValue());
				}catch(SQLException ex){
					ex.printStackTrace();
				}
		}
	}
	
	public void insertPAs(List<PostalAddressInfo> list){
		for(int i = 0; i< list.size();i++){
			try{
				db.execSQL(Global.insertIntoPA, list.get(i).getAllValue());
				}catch(SQLException ex){
					ex.printStackTrace();
				}
		}
	}
	
	public void insertOrgs(List<OrganizationInfo> list){
		for(int i = 0; i< list.size();i++){
			try{
				db.execSQL(Global.insertIntoOrg, list.get(i).getAllValue());
				}catch(SQLException ex){
					ex.printStackTrace();
				}
		}
	}
	
	public void insertIMs(List<ImInfo> list){
		for(int i = 0; i< list.size();i++){
			try{
				db.execSQL(Global.insertIntoIM, list.get(i).getAllValue());
				}catch(SQLException ex){
					ex.printStackTrace();
				}
		}
	}
	
	public void insertWebsites(List<WebsiteInfo> list){
		for(int i = 0; i< list.size();i++){
			try{
				db.execSQL(Global.insertIntoWebsite, list.get(i).getAllValue());
				}catch(SQLException ex){
					ex.printStackTrace();
				}
		}
	}
	
	public void onCreateAllTable() {
		// enable foreign key
		db.execSQL(Global.enableForeignKey);
		// create 7 table (the order is like Google Docs file)
		db.execSQL(Global.createUserTable);
		db.execSQL(Global.createPhoneTable);
		db.execSQL(Global.createEmailTable);
		db.execSQL(Global.createPostalAddressTable);
		db.execSQL(Global.createOrgTable);
		db.execSQL(Global.createIMTable);
		db.execSQL(Global.createWebsiteTable);
	}


//*******************************************************************************************************************
//*******************************************************************************************************************
	// Cac ham duoi day se lay thong tin tu DB BkitMobile de chuan bi cho viec insert vao Contact
	
	
	public List<UserInfo> getUserInfo(){// lay thong tin chung cua 1 user theo id
		List<UserInfo> list = new LinkedList<UserInfo>();
		Cursor cursor = db.rawQuery("SELECT * FROM user ", null);
		while(cursor.moveToNext()){
			// chua lay thong tin ve Image
			String name = cursor.getString(cursor.getColumnIndex("name"));//tham khao ten cac cot trong Global.java
			long id = cursor.getLong(cursor.getColumnIndex("id"));
			list.add(new UserInfo(id, name, "", ""));
		}
		cursor.close();
		return list;
	}
	public UserInfo getUserInfo(long userId){// lay thong tin chung cua 1 user theo id
		Cursor cursor = db.rawQuery("SELECT * FROM user WHERE id = " + userId, null);
		String name,note,nickname;
		name = note = nickname = null;
		
		while(cursor.moveToNext()){
			// chua lay thong tin ve Image
			name = cursor.getString(cursor.getColumnIndex("name"));//tham khao ten cac cot trong Global.java
			note = cursor.getString(cursor.getColumnIndex("note"));
			nickname = cursor.getString(cursor.getColumnIndex("nickname"));
//			Log.d("getUserInfo","name " + name + " note " + note + " id " + id);
		}
		cursor.close();
		return new UserInfo(userId, name, note, nickname);
	}
	public  List<PhoneInfo> getPhoneInfo(long userId){
		List<PhoneInfo> list = new LinkedList<PhoneInfo>();
		int type = -1;
		String value = null;
		Cursor cursor = db.rawQuery("SELECT * FROM phone WHERE id = " + userId, null);
		while(cursor.moveToNext()){
			type = cursor.getInt(cursor.getColumnIndex("type"));
			value = cursor.getString(cursor.getColumnIndex("value"));
			list.add(new PhoneInfo(userId, type, value));
//			Log.d("getPhoneInfo", userId + " aaaa " + type + " aaa " + value);
		}
		cursor.close();
		return list;
	}
	public List<EmailInfo> getEmailInfo(long userId){
		List<EmailInfo> list = new LinkedList<EmailInfo>();
		int type = -1;
		String value = null;
		Cursor cursor = db.rawQuery("SELECT * FROM email WHERE id = " + userId, null);
		while(cursor.moveToNext()){
			type = cursor.getInt(cursor.getColumnIndex("type"));
			value = cursor.getString(cursor.getColumnIndex("value"));
			list.add(new EmailInfo(userId, type, value));
//			Log.d("getEmailInfo", userId + " aaaa " + type + " aaa " + value);
		}
		cursor.close();
		return list;
	}
	public List<PostalAddressInfo> getPA(long userId){
		List<PostalAddressInfo> list = new LinkedList<PostalAddressInfo>();
		Cursor cursor = db.rawQuery("SELECT * FROM postal_address WHERE id = " + userId, null);
		int type;String street; String poBox; String neighborhood; String city;
		String state;String zipcode;String country;
		while(cursor.moveToNext()){
			type = cursor.getInt(cursor.getColumnIndex("type"));
			street = cursor.getString(cursor.getColumnIndex("street"));
			poBox = cursor.getString(cursor.getColumnIndex("poBox"));
			neighborhood = cursor.getString(cursor.getColumnIndex("neighborhood"));
			city = cursor.getString(cursor.getColumnIndex("city"));
			state = cursor.getString(cursor.getColumnIndex("state"));
			zipcode = cursor.getString(cursor.getColumnIndex("zipcode"));
			country = cursor.getString(cursor.getColumnIndex("country"));
			list.add(new PostalAddressInfo(
					userId, type, street, poBox, neighborhood, city, state, zipcode, country));
		}
		cursor.close();
		return list;
	}
	public List<OrganizationInfo> getOrg(long userId){
		List<OrganizationInfo> list = new LinkedList<OrganizationInfo>();
		Cursor cursor = db.rawQuery("SELECT * FROM organization WHERE id = " + userId, null);
		int type;String company;String title;
		while(cursor.moveToNext()){
			type = cursor.getInt(cursor.getColumnIndex("type"));
			company = cursor.getString(cursor.getColumnIndex("company"));
			title = cursor.getString(cursor.getColumnIndex("title"));
			list.add(new OrganizationInfo(userId, type, company, title));
		}
		cursor.close();
		return list;
	}
	public List<ImInfo> getIM(long userId){
		List<ImInfo> list = new LinkedList<ImInfo>();
		int type = -1;
		String value = null;
		Cursor cursor = db.rawQuery("SELECT * FROM im WHERE id = " + userId, null);
		while(cursor.moveToNext()){
			type = cursor.getInt(cursor.getColumnIndex("type"));
			value = cursor.getString(cursor.getColumnIndex("value"));
			list.add(new ImInfo(userId, type, value));
//			Log.d("getImInfo", userId + " aaaa " + type + " aaa " + value);
		}
		cursor.close();
		return list;
	}
	public List<WebsiteInfo> getWebsite(long userId){
		List<WebsiteInfo> list = new LinkedList<WebsiteInfo>();
		String value = null;
		Cursor cursor = db.rawQuery("SELECT * FROM website WHERE id = " + userId, null);
		while(cursor.moveToNext()){
			value = cursor.getString(cursor.getColumnIndex("value"));
			list.add(new WebsiteInfo(userId, value));
		}
		cursor.close();
		return list;
	}
	public void deleteUser(long id){
		db.delete(TABLE_USER, "id = ? ", new String[] {id+""});
	}
}
